# url 转码
from urllib import parse

# blueprint
from . import bp
# 对象转 json
from flask import jsonify

# 缓存
from flaskr import cache,cn_database

from .. import utils

# 数据链接
#db1 = cn_database.cn_database('192.168.199.250','sa','hdyy','hdemrdb','1433','mssql')
db1 = cn_database.cn_database('192.168.30.12','hdyy_sjpt','hdyy_sjpt12','orcl','1521','oracle')

# 测试api
@bp.route('/t_result/<int:id>', methods=['GET'])
@cache.memoize(timeout=50)
def get_user(id):
    print("test: id= %s"%(id))
    return jsonify({'user': id})

# 不带分页模糊查询 api
@bp.route('/t_results/<string:key_word>', methods=['GET'])
@cache.memoize(timeout=60*60*1)
def get_results(key_word):
    return jsonify({'datas:':get_data(key_word)})

# api 调用函数
@utils.metric
def get_data(key_word):
    kw = parse.urlparse(key_word).path
    str1 = 'Edit_SS_SZ5,Edit_SS_DENGJINAME5,Edit_SS_MC5,Edit_SS_BM5,Edit_SS_SZ4,Edit_SS_DENGJINAME4,Edit_SS_MC4,Edit_SS_BM4,Edit_SS_SZ3,Edit_SS_DENGJINAME3,Edit_SS_MC3,Edit_SS_BM3,Edit_SS_SZ2,Edit_SS_DENGJINAME2,Edit_SS_MC2,Edit_SS_BM2,Edit_SS_SZ1,Edit_SS_DENGJINAME1,Edit_SS_MC1,Edit_SS_BM1,Edit_QDZD3,Edit_QDZD3_ICD,Edit_QDZD2,Edit_QDZD2_ICD,Edit_QDZD1,Edit_QDZD1_ICD,zhuyuanno,mainmgrdoctoruid,mainmgrdoctorname,people_yiliaofukuantype_codeid,people_ruyuancount,binganhao,people_cardno,people_name,people_sexcodename,people_chushengdate,people_age1,people_guoji,people_babyagemonth,people_chushengtizhong,people_ruyuantizhong,people_chushengdi,people_jiguan,people_minzu,people_shenfenzhengno,people_zhiyename,people_hunyinname,people_hunyincode,people_xzdzaddress,people_xzdztel,people_xzdzpost,people_hk_address,people_hk_post,people_gzdw_address,people_gzdw_tel,people_gzdw_post,people_lxr_name,people_lxr_guanxi,people_lxr_address,people_lxr_tel,people_ruyuantujingcode,people_ruyuantujingname,people_ruyuan_unitid,people_ruyuan_unitname,people_ruyuan_bingshi,people_zhuankeunitname,people_chuyuan_date,people_chuyuan_unitid,people_chuyuan_unitname,people_chuyuan_bingshi,people_zhuyuantianshu,people_menzhenzhenduan_codeid,people_menzhenzd_codename,people_sunshangzhongdu_codeid,people_sunshangzd_codename,people_binglizhenduan_codeid,people_binglizhenduan_codename,people_binglihao,people_isyaowuguominid,people_isyaowuguominname,people_yaowuguominshi,shijian_codeid,xuexing_codeid,xuexing_codename,rh_codeid,rh_codename,kezhuren_codeid,kezhuren_codename,zhurenyishi_codeid,zhurenyishi_codename,zhuzhiyishi_codeid,zhuzhiyishi_codename,zhuyuanyishi_codeid,zhuyuanyishi_codename,zherenhushi_codeid,zherenhushi_codename,jinxiuyishi_codeid,jiuxiuyishi_codename,shixiyishi_codeid,shixiyishi_codename,bianma_codeid,bianma_codename,binganzhiliang_codeid,binganzhiliang_codename,zhikongyishi_codeid,zhikongyishi_codename,zhikonghushi_codeid,zhikonghushi_codename,zhikong_date,liyuanfangshiname,liyuanfangshicode,jieshouyiyuan,jieshouyiyuan1,zhuyuanagainname,zhuyuanagaincode,zhuyuanmudi,edit_zyzd_icd,edit_zyzd,cyzd_zd0_codevalue,edit_zyzd_ryqk,cyzd_zd0_cycodevalue,edit_zyzd_cyqk'
    sqlstr = """select * from
(
  select %s,row_number() over(PARTITION by zhuyuanno order by zhuyuanno ) rn from bingli_info t
 where 
      zhuyuanno like '%%%s%%'     --病人住院流水号 
      or binganhao like '%%%s%%'  --病人病案号
      or mainmgrdoctoruid like '%%%s%%' --管床医生ID
      or mainmgrdoctorname like '%%%s%%' --管床医生姓名 
      or people_cardno like '%%%s%%'--健康卡号
      or people_name like '%%%s%%'--病人姓名
      or people_shenfenzhengno like '%%%s%%' --病人身份证号
      or paragraph_text like '%%%s%%' --病历段落内容
      or people_ruyuan_unitname like '%%%s%%' --入院科室
      or Edit_ZYZD_ICD = '%s' --第一诊断ICD编码
      or Edit_ZYZD like '%%%s%%' --第一诊断ICD名称
      or Edit_QDZD1_ICD = '%s' --第二诊断ICD编码
      or Edit_QDZD1 like '%%%s%%' --第二断ICD名称
      or Edit_QDZD2_ICD = '%s' --第三诊断ICD编码
      or Edit_QDZD2 like '%%%s%%' --第三断ICD名称
      or Edit_QDZD3_ICD = '%s' --第四诊断ICD编码
      or Edit_QDZD3 like '%%%s%%' --第四断ICD名称
      or Edit_SS_BM1 ='%s' --手术编码
      or Edit_SS_MC1 like '%%%s%%' --手术名称
      or Edit_SS_DENGJINAME1 like '%%%s%%' --手术等级名称
      or Edit_SS_SZ1 like '%%%s%%' --术者
      or Edit_SS_BM2 ='%s' --手术编码
      or Edit_SS_MC2 like '%%%s%%' --手术名称
      or Edit_SS_DENGJINAME2 like '%%%s%%' --手术等级名称
      or Edit_SS_SZ2 like '%%%s%%' --术者
      or Edit_SS_BM3 ='%s' --手术编码
      or Edit_SS_MC3 like '%%%s%%' --手术名称
      or Edit_SS_DENGJINAME3 like '%%%s%%' --手术等级名称
      or Edit_SS_SZ3 like '%%%s%%' --术者
      or Edit_SS_BM4 ='%s' --手术编码
      or Edit_SS_MC4 like '%%%s%%' --手术名称
      or Edit_SS_DENGJINAME4 like '%%%s%%' --手术等级名称
      or Edit_SS_SZ4 like '%%%s%%' --术者
      or Edit_SS_BM5 ='%s' --手术编码
      or Edit_SS_MC5 like '%%%s%%' --手术名称
      or Edit_SS_DENGJINAME5 like '%%%s%%' --手术等级名称
      or Edit_SS_SZ5 like '%%%s%%' --术者
      ) X where rn = 1 and rownum < 10000
	  """ % (str1,kw,kw, kw,kw, kw, kw,kw, kw, kw,kw, kw, kw,kw, kw, kw,kw, kw, kw,kw, kw, kw,kw, kw, kw,kw, kw, kw, kw,kw, kw, kw, kw, kw, kw,kw, kw, kw)
    print(sqlstr)
    reslist = db1.exec_query(sqlstr)
    lkeys = str1.split(',')
    lkeys.append('rowNumber')
    #print(lkeys)
    #print(reslist)
    datas=[]
    for res in reslist:
        data = dict(zip(lkeys, list(res)))
        datas.append(data)
    return datas


# 带分页模糊查询 api
@bp.route('/t_results/<string:key_word>/<int:page_length>/<int:page_num>', methods=['GET'])
@cache.memoize(timeout=60*60*1)
def get_results_with_page(key_word,page_length,page_num):
    return jsonify({'datas:':get_data_with_page(key_word,page_length,page_num)})

# API 调用函数
@utils.metric
def get_data_with_page(key_word,page_length,page_num):
    if page_length == 0 or page_num==0:
        raise(NameError,"分页相关参数不能小于0")
    kw = parse.urlparse(key_word).path
    str1 = 'Edit_SS_SZ5,Edit_SS_DENGJINAME5,Edit_SS_MC5,Edit_SS_BM5,Edit_SS_SZ4,Edit_SS_DENGJINAME4,Edit_SS_MC4,Edit_SS_BM4,Edit_SS_SZ3,Edit_SS_DENGJINAME3,Edit_SS_MC3,Edit_SS_BM3,Edit_SS_SZ2,Edit_SS_DENGJINAME2,Edit_SS_MC2,Edit_SS_BM2,Edit_SS_SZ1,Edit_SS_DENGJINAME1,Edit_SS_MC1,Edit_SS_BM1,Edit_QDZD3,Edit_QDZD3_ICD,Edit_QDZD2,Edit_QDZD2_ICD,Edit_QDZD1,Edit_QDZD1_ICD,zhuyuanno,mainmgrdoctoruid,mainmgrdoctorname,people_yiliaofukuantype_codeid,people_ruyuancount,binganhao,people_cardno,people_name,people_sexcodename,people_chushengdate,people_age1,people_guoji,people_babyagemonth,people_chushengtizhong,people_ruyuantizhong,people_chushengdi,people_jiguan,people_minzu,people_shenfenzhengno,people_zhiyename,people_hunyinname,people_hunyincode,people_xzdzaddress,people_xzdztel,people_xzdzpost,people_hk_address,people_hk_post,people_gzdw_address,people_gzdw_tel,people_gzdw_post,people_lxr_name,people_lxr_guanxi,people_lxr_address,people_lxr_tel,people_ruyuantujingcode,people_ruyuantujingname,people_ruyuan_unitid,people_ruyuan_unitname,people_ruyuan_bingshi,people_zhuankeunitname,people_chuyuan_date,people_chuyuan_unitid,people_chuyuan_unitname,people_chuyuan_bingshi,people_zhuyuantianshu,people_menzhenzhenduan_codeid,people_menzhenzd_codename,people_sunshangzhongdu_codeid,people_sunshangzd_codename,people_binglizhenduan_codeid,people_binglizhenduan_codename,people_binglihao,people_isyaowuguominid,people_isyaowuguominname,people_yaowuguominshi,shijian_codeid,xuexing_codeid,xuexing_codename,rh_codeid,rh_codename,kezhuren_codeid,kezhuren_codename,zhurenyishi_codeid,zhurenyishi_codename,zhuzhiyishi_codeid,zhuzhiyishi_codename,zhuyuanyishi_codeid,zhuyuanyishi_codename,zherenhushi_codeid,zherenhushi_codename,jinxiuyishi_codeid,jiuxiuyishi_codename,shixiyishi_codeid,shixiyishi_codename,bianma_codeid,bianma_codename,binganzhiliang_codeid,binganzhiliang_codename,zhikongyishi_codeid,zhikongyishi_codename,zhikonghushi_codeid,zhikonghushi_codename,zhikong_date,liyuanfangshiname,liyuanfangshicode,jieshouyiyuan,jieshouyiyuan1,zhuyuanagainname,zhuyuanagaincode,zhuyuanmudi,edit_zyzd_icd,edit_zyzd,cyzd_zd0_codevalue,edit_zyzd_ryqk,cyzd_zd0_cycodevalue,edit_zyzd_cyqk'
    sqlstr = """select * from
(
  select %s,row_number() over(PARTITION by zhuyuanno order by zhuyuanno ) rn from bingli_info t
 where 
      zhuyuanno like '%%%s%%'     --病人住院流水号 
      or binganhao like '%%%s%%'  --病人病案号
      or mainmgrdoctoruid like '%%%s%%' --管床医生ID
      or mainmgrdoctorname like '%%%s%%' --管床医生姓名 
      or people_cardno like '%%%s%%'--健康卡号
      or people_name like '%%%s%%'--病人姓名
      or people_shenfenzhengno like '%%%s%%' --病人身份证号
      or paragraph_text like '%%%s%%' --病历段落内容
      or people_ruyuan_unitname like '%%%s%%' --入院科室
      or Edit_ZYZD_ICD = '%s' --第一诊断ICD编码
      or Edit_ZYZD like '%%%s%%' --第一诊断ICD名称
      or Edit_QDZD1_ICD = '%s' --第二诊断ICD编码
      or Edit_QDZD1 like '%%%s%%' --第二断ICD名称
      or Edit_QDZD2_ICD = '%s' --第三诊断ICD编码
      or Edit_QDZD2 like '%%%s%%' --第三断ICD名称
      or Edit_QDZD3_ICD = '%s' --第四诊断ICD编码
      or Edit_QDZD3 like '%%%s%%' --第四断ICD名称
      or Edit_SS_BM1 ='%s' --手术编码
      or Edit_SS_MC1 like '%%%s%%' --手术名称
      or Edit_SS_DENGJINAME1 like '%%%s%%' --手术等级名称
      or Edit_SS_SZ1 like '%%%s%%' --术者
      or Edit_SS_BM2 ='%s' --手术编码
      or Edit_SS_MC2 like '%%%s%%' --手术名称
      or Edit_SS_DENGJINAME2 like '%%%s%%' --手术等级名称
      or Edit_SS_SZ2 like '%%%s%%' --术者
      or Edit_SS_BM3 ='%s' --手术编码
      or Edit_SS_MC3 like '%%%s%%' --手术名称
      or Edit_SS_DENGJINAME3 like '%%%s%%' --手术等级名称
      or Edit_SS_SZ3 like '%%%s%%' --术者
      or Edit_SS_BM4 ='%s' --手术编码
      or Edit_SS_MC4 like '%%%s%%' --手术名称
      or Edit_SS_DENGJINAME4 like '%%%s%%' --手术等级名称
      or Edit_SS_SZ4 like '%%%s%%' --术者
      or Edit_SS_BM5 ='%s' --手术编码
      or Edit_SS_MC5 like '%%%s%%' --手术名称
      or Edit_SS_DENGJINAME5 like '%%%s%%' --手术等级名称
      or Edit_SS_SZ5 like '%%%s%%' --术者
      ) X where rn = 1
      and rownum > %d and rownum < %d
    """ % (str1,kw,kw, kw,kw, kw, kw,kw, kw, kw,kw, kw, kw,kw, kw, kw,kw, kw, kw,kw, kw, kw,kw, kw, kw,kw, kw, kw, kw,kw, kw, kw, kw, kw, kw,kw, kw, kw,(page_num-1)*page_length,page_num*page_length+1)
    print(sqlstr)
    reslist = db1.exec_query(sqlstr)
    lkeys = str1.split(',')
    lkeys.append('rowNumber')
    #print(lkeys)
    #print(reslist)
    datas=[]
    for res in reslist:
        data = dict(zip(lkeys, list(res)))
        datas.append(data)
    return datas
